package DAO;

import javabean.Bill;

import java.sql.*;
import java.util.*;
import java.util.Date;

public class BillDAO implements IBillDAO{
    @Override
    public List<Bill> getBillList(String customerID) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Bill> billList = new ArrayList<>();

        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM Bill WHERE CustomerID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, customerID);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                int billID = rs.getInt("BillID");
                int orderID = rs.getInt("OrderID");
                double price = rs.getDouble("Price");
                String paymentMethod = rs.getString("PaymentMethod");
                Date createdDate = rs.getTimestamp("CreatedDate");
                Date modifiedDate = rs.getTimestamp("ModifiedDate");

                Bill bill = new Bill();
                bill.setBillID(billID);
                bill.setCustomerID(customerID);
                bill.setOrderID(orderID);
                bill.setPrice(price);
                bill.setPaymentMethod(paymentMethod);
                bill.setCreatedDate(createdDate);
                bill.setModifiedDate(modifiedDate);

                billList.add(bill);
            }
        } catch (SQLException e) {
            throw new SQLException("Failed to get bill list. " + e.getMessage());
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }

        return billList;
    }


    @Override
    public boolean addBill(Bill bill) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean isAdded = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "INSERT INTO Bill (CustomerID, OrderID, Price, PaymentMethod, CreatedDate, ModifiedDate) " +
                    "VALUES (?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, bill.getCustomerID());
            pstmt.setInt(2, bill.getOrderID());
            pstmt.setDouble(3, bill.getPrice());
            pstmt.setString(4, bill.getPaymentMethod());
            pstmt.setDate(5, new java.sql.Date(bill.getCreatedDate().getTime()));
            pstmt.setDate(6, new java.sql.Date(bill.getModifiedDate().getTime()));

            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("账单添加成功");
                isAdded = true;
            } else {
                System.out.println("账单添加失败");
                isAdded = false;
            }
        } catch (SQLException e) {
            throw new SQLException("Failed to add bill. " + e.getMessage());
        } finally {
            ConnectManager.closeConnection(conn, pstmt, null);
        }

        return isAdded;
    }

    @Override
    public boolean deleteBill(int billID) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean isDeleted = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "DELETE FROM Bill WHERE BillID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, billID);

            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("账单删除成功");
                isDeleted = true;
            } else {
                System.out.println("账单删除失败");
                isDeleted = false;
            }
        } catch (SQLException e) {
            throw new SQLException("Failed to delete bill. " + e.getMessage());
        } finally {
            ConnectManager.closeConnection(conn, pstmt, null);
        }

        return isDeleted;
    }

    @Override
    public boolean updateBill(Bill bill) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean isUpdated = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "UPDATE Bill SET CustomerID=?, OrderID=?, Price=?, PaymentMethod=?, CreatedDate=?, ModifiedDate=? " +
                    "WHERE BillID=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, bill.getCustomerID());
            pstmt.setInt(2, bill.getOrderID());
            pstmt.setDouble(3, bill.getPrice());
            pstmt.setString(4, bill.getPaymentMethod());
            pstmt.setDate(5, new java.sql.Date(bill.getCreatedDate().getTime()));
            pstmt.setDate(6, new java.sql.Date(bill.getModifiedDate().getTime()));
            pstmt.setInt(7, bill.getBillID());

            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("账单更新成功");
                isUpdated = true;
            } else {
                System.out.println("账单更新失败");
                isUpdated = false;
            }
        } catch (SQLException e) {
            throw new SQLException("Failed to update bill. " + e.getMessage());
        } finally {
            ConnectManager.closeConnection(conn, pstmt, null);
        }

        return isUpdated;
    }

    @Override
    public Bill getBill(int billID) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Bill bill = null;

        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM Bill WHERE BillID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, billID);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                String customerID = rs.getString("CustomerID");
                int orderID = rs.getInt("OrderID");
                double price = rs.getDouble("Price");
                String paymentMethod = rs.getString("PaymentMethod");
                Date createdDate = rs.getDate("CreatedDate");
                Date modifiedDate = rs.getDate("ModifiedDate");

                bill = new Bill();
                bill.setBillID(billID);
                bill.setCustomerID(customerID);
                bill.setOrderID(orderID);
                bill.setPrice(price);
                bill.setPaymentMethod(paymentMethod);
                bill.setCreatedDate(createdDate);
                bill.setModifiedDate(modifiedDate);
            }
        } catch (SQLException e) {
            throw new SQLException("Failed to get bill. " + e.getMessage());
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }

        return bill;
    }

    @Override
    public boolean balanceChange(Bill bill) throws Exception{
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean isInserted = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "INSERT INTO Bill(CustomerID, Price, PaymentMethod, CreatedDate, ModifiedDate) " +
                    "VALUES (?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, bill.getCustomerID());
            pstmt.setDouble(2, bill.getPrice());
            pstmt.setString(3, bill.getPaymentMethod());
            pstmt.setTimestamp(4, new Timestamp(bill.getCreatedDate().getTime()));
            pstmt.setTimestamp(5, new Timestamp(bill.getModifiedDate().getTime()));

            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("账单插入成功");
                isInserted = true;
            } else {
                System.out.println("账单插入失败");
                isInserted = false;
            }
        } catch (SQLException e) {
            throw new SQLException("Failed to insert bill. " + e.getMessage());
        } finally {
            ConnectManager.closeConnection(conn, pstmt, null);
        }

        return isInserted;
    }
}
